package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Material;
import model.Proveedor;

public class MaterialMapper implements DataMapper<Material>{
	private static MaterialMapper instance;
	private ProveedorMapper proveedorMapper;
	private MaterialMapper(){
		proveedorMapper = ProveedorMapper.getInstance();
	}
	
	public static MaterialMapper getInstance() {
		if(instance == null) {
			instance = new MaterialMapper();
		}
		return instance;
	}
	
	@Override
	public boolean insertar(Material material) {
		boolean result = false;
		Connection connection = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "INSERT INTO material (codigo, nombre, cantidad_stock, punto_de_pedido, costo, proveedor_id) VALUES (?,?,?,?,?,?)";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1, material.getCodigo());
			ps.setString(2, material.getNombre());
			ps.setInt(3, material.getCantidadStock());
			ps.setInt(4, material.getPuntoDePedido());
			ps.setFloat(5, material.getCosto());
			ps.setLong(6, material.getProveedor().getProveedorId());
			ps.execute();
			
			PoolConnection.getPoolConnection().releaseConnection(connection);
			result = true;
		} catch (SQLException e) {
			PoolConnection.getPoolConnection().releaseConnection(connection);
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public boolean modificar(Material material) {
		boolean result = false;
		Connection connection = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "UPDATE material set codigo = ?, nombre = ?, cantidad_stock = ?, punto_de_pedido = ?, costo = ?, proveedor_id  = ? where material_id = ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setString(1, material.getCodigo());
			ps.setString(2, material.getNombre());
			ps.setInt(3, material.getCantidadStock());
			ps.setInt(4, material.getPuntoDePedido());
			ps.setFloat(5, material.getCosto());
			ps.setLong(6, material.getProveedor().getProveedorId());
			ps.setLong(7, material.getMaterialId());
			ps.execute();
			
			PoolConnection.getPoolConnection().releaseConnection(connection);
			result = true;
		} catch (SQLException e) {
			PoolConnection.getPoolConnection().releaseConnection(connection);
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public boolean eliminar(Material material) {
		boolean result = false;
		Connection connection = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "UPDATE material set is_vigente = 0 where material_id = ?";
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setLong(1, material.getMaterialId());
			ps.execute();
			
			PoolConnection.getPoolConnection().releaseConnection(connection);
			result = true;
		} catch (SQLException e) {
			PoolConnection.getPoolConnection().releaseConnection(connection);
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public Material buscar(Long materialId) {
		Material material = null;
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "SELECT * from material where material_id = ?";
			PreparedStatement ps = null;
			ps = con.prepareStatement(query);
			ps.setLong(1, materialId);
			ResultSet result = ps.executeQuery();
			
			if(result.next()) {
				Proveedor proveedor = proveedorMapper.buscar(result.getLong("proveedor_id"));
				material = new Material(result.getString("codigo"),result.getString("nombre"), result.getInt("cantidad_stock"), result.getInt("punto_de_pedido"), proveedor, result.getFloat("costo"));
				material.setMaterialId(result.getLong("material_id"));
			}
			
			PoolConnection.getPoolConnection().releaseConnection(con);

		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar Material: " + e.getMessage());
			System.out.println("Stack Trace al buscar Material: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(con);
		}
		return material;
	}
	
	public List<Material> buscarMaterialesProvistos(Proveedor proveedor) {
		List<Material> materiales = new ArrayList<Material>();
		Connection con = PoolConnection.getPoolConnection().getConnection();
		
		try {
			String query = "SELECT * from material  where proveedor_id = ? and is_vigente = 1";
			PreparedStatement ps = null;
			ps = con.prepareStatement(query);
			ps.setLong(1, proveedor.getProveedorId());
			ResultSet result = ps.executeQuery();
			
			while(result.next()) {
				Material material = new Material(result.getString("codigo"),result.getString("nombre"), result.getInt("cantidad_stock"), result.getInt("punto_de_pedido"), proveedor, result.getFloat("costo"));
				material.setMaterialId(result.getLong("material_id"));
				materiales.add(material);
			}
			PoolConnection.getPoolConnection().releaseConnection(con);
		} catch (SQLException e) {
			System.out.println("Mensaje Error al buscar Materiales provistos por un proveedor: " + e.getMessage());
			System.out.println("Stack Trace al buscar Materiales provistos por un proveedor: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(con);
		}
		
		return materiales;
	}

	@Override
	public List<Material> buscarTodos() {
		List<Material> materiales = new ArrayList<Material>();
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "SELECT * from material where is_vigente = 1";
			PreparedStatement ps = null;
			ps = con.prepareStatement(query);

			ResultSet result = ps.executeQuery();
			while(result.next()) {
				Proveedor proveedor = proveedorMapper.buscar(result.getLong("proveedor_id"));
				Material material = new Material(result.getString("codigo"),result.getString("nombre"), result.getInt("cantidad_stock"), result.getInt("punto_de_pedido"), proveedor, result.getFloat("costo"));
				material.setMaterialId(result.getLong("material_id"));
				materiales.add(material);
			}
			PoolConnection.getPoolConnection().releaseConnection(con);

		} catch (SQLException e) {
			System.out.println("Mensaje Error Buscar todos los Materiales: " + e.getMessage());
			System.out.println("Stack Trace al Buscar todos los Materiales: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(con);
		}
		return materiales;
	}
	
	/**
	 * Actualizamos el stock de un material sumando la cantidad indicada en la orden de compra.
	 * 
	 * @param material
	 * @param cantidad
	 * @return
	 */
	public boolean actualizarStock(Material material, int cantidad) {
		boolean result = false;
		Connection con = PoolConnection.getPoolConnection().getConnection();

		try {
			String query = "UPDATE material set cantidad_stock = ? where material_id = ?";
			PreparedStatement ps = con.prepareStatement(query);
			int stock = material.getCantidadStock() + cantidad;
			ps.setInt(1, stock);
			ps.setLong(2, material.getMaterialId());
			ps.execute();
			
			PoolConnection.getPoolConnection().releaseConnection(con);
			result = true;
		} catch (SQLException e) {
			System.out.println("Mensaje Error al actualizar stock Material: " + e.getMessage());
			System.out.println("Stack Trace al actualizar stock Material: " + e.getStackTrace());
			PoolConnection.getPoolConnection().releaseConnection(con);
		}
		
		return result;
		
	}
}